<?php
class m_usertracking extends Zmodel{
    function __construct() {
   	    parent::__construct();
    }
	
    function getVisitorAll($num,$offset)
    {	
    	$this->load->database();
    	if($offset!=""){
    		$sql="SELECT ip, hits_date FROM hits_counter ORDER BY hits_date DESC LIMIT ".$num." OFFSET ".$offset."";
    	}else {
    		$sql="SELECT ip, hits_date FROM hits_counter ORDER BY hits_date DESC LIMIT ".$num." OFFSET 0";
    	}
    	return $this->db->query($sql);
    }
    
    function getVisitorDate($startDate,$endDate,$num=null,$offset=null)
    {
		if($offset!=""){
    	$sql="SELECT ip, 
    			     hits_date
    		  FROM	 hits_counter		  
    		  WHERE  hits_date>='".$startDate."' 
    		  AND 	 hits_date<='".$endDate."'
    		  ORDER BY
				 	 hits_date DESC LIMIT ".$num." OFFSET ".$offset."";	
    	}else{
    		  $sql="SELECT ip, 
    			     hits_date
    		  FROM	 hits_counter		  
    		  WHERE  hits_date>='".$startDate."' 
    		  AND 	 hits_date<='".$endDate."'
    		  ORDER BY
			     	 hits_date DESC LIMIT ".$num." OFFSET 0";	
    	}
    	
    	return $this->db->query($sql);
    }
    
    function getVisitor_total_rows($startDate,$endDate)
    {
    	  $sql="SELECT count(ip) AS nb_ip FROM hits_counter WHERE hits_date>='".$startDate."' AND hits_date<='".$endDate."'";
    	  $query = $this->db->query($sql);
    	  $result =  $query->row_array();
    	  return $result["nb_ip"];
    	  
    	  //foreach ($query->result_array() as $row)
		  //{
		  // return $ip = $row['ip'];
		  //}
		  
    }
    
    function getVisitorYesterday(){
    	//$Yday = time() - (3600*48);
    	//$today = time() - (3600*24);
    	
		$today = date('Y-m-d',time());
		//$yesterday = date('Y-m-d',time() - 3600);
		$yesterday = strtotime("yesterday");
		$yesterday = date("Y-m-d", $yesterday);
    	$sql="SELECT *
    		  FROM hits_counter		  
    		  WHERE hits_date 
    		  BETWEEN '".$yesterday."' 
    		  AND '".$today."'";
    	return $this->db->query($sql);
    }
    
    function getVisitorToday(){
		$today = date('Y-m-d',time());
		$tomorrow = mktime(0, 0, 0, date("m"), date("d")+1, date("y"));
		//"Tomorrow is ".date("Y/m/d", $tomorrow); 
		
		$tomorrow = date("Y-m-d", $tomorrow);
    	$sql="SELECT *
    		  FROM hits_counter		  
    		  WHERE hits_date
    		  BETWEEN '".$today."'
    		  AND '".$tomorrow."'";
    	$query=$this->db->query($sql);
    	return $query->num_rows();
    	//return $this->db->query($sql);
    }
    
    function getVisitorLastWeek(){
    	//$Yday = time() - (3600*48);
    	//$today = time() - (3600*24);
    	
		$lastWeek = date('Y-m-d H:i:s',time()-(7*86400));
		$today = date('Y-m-d',time());
    	$sql="SELECT *
    		  FROM hits_counter		  
    		  WHERE hits_date 
    		  BETWEEN '".$lastWeek."' 
    		  AND '".$today."'";
    	$query=$this->db->query($sql);
    	return $query->num_rows();
    }
    
    function getVisitorLastMonth(){
    	//$Yday = time() - (3600*48);
    	//$today = time() - (3600*24);
    	
		$lastMonth = date("Y-m-d", strtotime("last month"));
		$today = date('Y-m-d',time());
    	$sql="SELECT *
    		  FROM hits_counter		  
    		  WHERE hits_date 
    		  BETWEEN '".$lastMonth."' 
    		  AND '".$today."'";
    	$query=$this->db->query($sql);
    	return $query->num_rows();
    }
    //(SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
    //(SELECT COUNT(com_id) FROM hit_com_ads WHERE com_id = 1) as count_ad
	function all_hit_com_ads(){
		$sql = "SELECT id,
				  com_id,
				  com_name,
				  com_logo,
				  com_url,
				  com_img,
				  com_date
				FROM hit_com_ads_cat";
		return $this->db->query($sql);	
	}
	
	/*function hit_com_ads_id($ID){
	 	$sql = "SELECT com_id
	 			FROM hit_ads
	 			WHERE com_id=".$ID;
	 	$query = $this->db->query($sql);
	 	return $query->num_rows();	
	}*/
	
	function hit_com_ads_id(){
		$com_id =1;
		if(isset($_POST["startDate"])){
			$startDate = $_POST["startDate"];
			$endDate = $_POST["endDate"];
		}
	 	$sql = "SELECT a.ads_id, 
					  a.ads_desc, 
					  a.com_id, 
					  c.com_name,
					  COUNT(h.hit_id) as nb_hit
					FROM hit_ads h,
					  advertising a
					LEFT JOIN hit_company c ON c.com_id = a.com_id
					WHERE h.ads_id = a.ads_id";
					if(isset($startDate) AND $startDate!=""){
						$sql .=" AND h.hit_date BETWEEN '".$startDate."' AND ";
						if($endDate==""){
							$sql .="CURRENT_TIMESTAMP";
						}else {
							$sql .="'".$endDate."'";
							}
					}
					$sql .=" GROUP BY a.ads_id, a.ads_desc, a.com_id, c.com_name
					ORDER BY a.ads_id asc";
	 	$query = $this->db->query($sql);
	 	return $query->result_array();	
	}
	
	function insert_com_ads($id){
		$sql = "INSERT INTO advertising(ads_desc,com_id,ads_images,status) 
       			VALUES(".$id.",CURRENT_TIMESTAMP)";
		return $this->db->query($sql);
	}
	
	function get_com_ads(){
		  $sql= "SELECT ads_id,
					  ads_desc,
					  com_id,
					  ads_images,
					  ads_type,
					  status,
					  url
				 FROM advertising
				 WHERE status='1' AND ads_type='3'";
		  return $this->db->query($sql);
	}
	
	function get_com_ads_id($id){
		  $sql= "SELECT ads_id,
					  ads_desc,
					  com_id,
					  ads_images,
					  ads_type,
					  status,
					  url
				 FROM advertising
				 WHERE status='1' AND ads_type='3'
		  		 AND ads_id=".$id."" ;
		  $query = $this->db->query($sql);
		  return $query->result_array();
	}
	
	function get_company(){
		$sql="SELECT com_id,
					 com_name,
					  com_logo,
					  com_url,
					  com_date,
					  ads_type,
					  com_order
			 FROM hit_company"; 
  		return $this->db->query($sql);
	}
	
	function insert_hit_count($ads_id){
		$sql = "INSERT INTO hit_ads(ads_id,hit_date) 
       			VALUES(".$ads_id.",CURRENT_TIMESTAMP)";
		return $this->db->query($sql);
	}
}